﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Data.Models;
using System.Collections.Generic;
using AccessControl.Contracts.Models;

namespace AccessControl.DataManagers.Tables.Managers
{
	/// <summary>
	/// Manages the maps in the DB
	/// </summary>
	class MapsManager : TableManager<IMap>
	{
		#region Fields

		private List<IMap> _maps;
		private static MapsManager s_Instance = null;
		private SqlDataAdapter _mapsAdapter, _entrisOnMapAdapter;

		private static readonly string COL_1_ID = "ID";
		private static readonly string COL_2_IMAGE_BYTES = "IMAGE_BYTES";
		
		private static readonly string COL_1_MAP_ID = "MAP_ID";
		private static readonly string COL_2_ENTRY_ID = "ENTRY_ID";
		private static readonly string COL_3_X = "LEFT_POSITION";
		private static readonly string COL_4_Y = "TOP_POSITION";
		

		#endregion

		#region Private Constructor

		/// <summary>
		/// Prevents a default instance of the <see cref="MapsManager"/> class from being created.
		/// </summary>
		private MapsManager()
		{
		}

		#endregion

		#region Public Properties

		/// <summary>
		/// Gets the instance.
		/// </summary>
		public static MapsManager Instance
		{
			get
			{
				lock (typeof(MapsManager))
				{
					if (s_Instance == null)
						s_Instance = new MapsManager();

					return s_Instance;
				}
			}
		}

		#endregion

		#region TableManager Overrides

		/// <summary>
		/// Creates using the specified connection.
		/// </summary>
		/// <param name="connection">The connection.</param>
		public override void Create(SqlConnection connection)
		{
			/* Create the maps table */
			SqlCommand command = connection.CreateCommand();
			command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} TEXT)", TablesNames.MAPS, COL_1_ID, COL_2_IMAGE_BYTES);
			command.ExecuteNonQuery();

			/* And the entries on map table */
			command = connection.CreateCommand();
			command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} INT, {3} INT, {4} INT)", 
												TablesNames.ENTRIES_ON_MAP, COL_1_MAP_ID, COL_2_ENTRY_ID, COL_3_X, COL_4_Y);
			command.ExecuteNonQuery();
		}

		/// <summary>
		/// Initializes this instance.
		/// </summary>
		public override void Initialize(SqlConnection connection)
		{
			/* Create the maps adapter */
			_mapsAdapter = new SqlDataAdapter();
			/* Fill the commands */
			_mapsAdapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.MAPS), connection);
			_mapsAdapter.InsertCommand = new SqlCommand(string.Format("INSERT INTO {0} ({1}, {2}) VALUES (@{1}, @{2})",
														TablesNames.MAPS, COL_1_ID, COL_2_IMAGE_BYTES), connection);
			_mapsAdapter.UpdateCommand = new SqlCommand(string.Format("UPDATE {0} SET {2} = @{2} WHERE {1} = @{1}",
														TablesNames.MAPS, COL_1_ID, COL_2_IMAGE_BYTES), connection);
			_mapsAdapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}", TablesNames.MAPS, COL_1_ID), connection);

			/* Now create the entries on map adapter */
			_entrisOnMapAdapter = new SqlDataAdapter();
			/* And fill the commands */
			_entrisOnMapAdapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.ENTRIES_ON_MAP), connection);
			_entrisOnMapAdapter.InsertCommand = new SqlCommand(string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES (@{1}, @{2}, @{3}, @{4})",
																TablesNames.ENTRIES_ON_MAP, COL_1_MAP_ID, COL_2_ENTRY_ID, COL_3_X, COL_4_Y), connection);
			_entrisOnMapAdapter.UpdateCommand = new SqlCommand(string.Format("UPDATE {0} SET {3} = @{3}, {4} = @{4} WHERE {1} = @{1} AND {2} = @{2}",
																TablesNames.ENTRIES_ON_MAP, COL_1_MAP_ID, COL_2_ENTRY_ID, COL_3_X, COL_4_Y), connection);
			_entrisOnMapAdapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}",
															   TablesNames.ENTRIES_ON_MAP, COL_1_MAP_ID), connection);
		}

		/// <summary>
		/// Gets the list.
		/// </summary>
		/// <returns></returns>
		public override bool FillList(IList<IMap> items)
		{
			Map map;
			IMapEntry mapEntry;
			List<IEntry> entries = new List<IEntry>();

			/* Fill the entries list */
			EntriesManager.Instance.FillList(entries);

			/* First fill the maps */
			using (SqlDataReader mapsReader = _mapsAdapter.SelectCommand.ExecuteReader())
			{
				/* Run on the maps */
				while (mapsReader.Read())
				{
					map = new Map()
					{
						Id = mapsReader.GetInt32(0),
						MapImageBytes = Convert.FromBase64String(mapsReader.GetString(1))
					};

					/* Add to the list */
					items.Add(map);
				}
			}

			/* Now fill the entries for the map */
			using (SqlDataReader entriesReader = _entrisOnMapAdapter.SelectCommand.ExecuteReader())
			{
				while (entriesReader.Read())
				{
					foreach (IMap mapItem in items)
					{
						/* If this is for our map */
						if (entriesReader.GetInt32(0) == mapItem.Id)
						{
							/* Create the map entry */
							mapEntry = CreateMapEntry(entriesReader.GetInt32(1), (double)entriesReader.GetInt32(2), (double)entriesReader.GetInt32(3), entries);
							/* And add if valid */
							if (mapEntry != null)
								mapItem.Entries.Add(mapEntry);
						}
					}
				}
			}
			
			return true;
		}

		/// <summary>
		/// Adds the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Add(IMap item)
		{
			/* First we add the map */
			_mapsAdapter.InsertCommand.Parameters.Clear();

			/* Set the item id */
			item.Id = GetHighestId(_mapsAdapter.SelectCommand.Connection, TablesNames.MAPS, COL_1_ID) + 1;

			_mapsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_mapsAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_IMAGE_BYTES, Convert.ToBase64String(item.MapImageBytes)));
			_mapsAdapter.InsertCommand.ExecuteNonQuery();

			/* And now the entries on map */
			foreach (IMapEntry mapEntry in item.Entries)
			{
				_entrisOnMapAdapter.InsertCommand.Parameters.Clear();

				/* Set the item id */
				mapEntry.Id = GetHighestId(_entrisOnMapAdapter.SelectCommand.Connection, TablesNames.ENTRIES_ON_MAP, COL_2_ENTRY_ID) + 1;

				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_MAP_ID, item.Id));
				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_ENTRY_ID, mapEntry.Id));
				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_3_X, (int)mapEntry.PositionOnMap.X));
				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_4_Y, (int)mapEntry.PositionOnMap.Y));
				_entrisOnMapAdapter.InsertCommand.ExecuteNonQuery();
			}

			return true;
		}

		/// <summary>
		/// Updates the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Update(IMap item)
		{
			/* First we add the map */
			_mapsAdapter.UpdateCommand.Parameters.Clear();
			_mapsAdapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_mapsAdapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_2_IMAGE_BYTES, Convert.ToBase64String(item.MapImageBytes)));
			_mapsAdapter.UpdateCommand.ExecuteNonQuery();

			/* And now the entries on map - first we delete all of them */
			foreach (IMapEntry mapEntry in item.Entries)
			{
				_entrisOnMapAdapter.DeleteCommand.Parameters.Clear();
				_entrisOnMapAdapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_MAP_ID, item.Id));				
				_entrisOnMapAdapter.DeleteCommand.ExecuteNonQuery();
			}

			/* And now we add again */
			foreach (IMapEntry mapEntry in item.Entries)
			{
				_entrisOnMapAdapter.InsertCommand.Parameters.Clear();
				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_MAP_ID, item.Id));
				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_ENTRY_ID, mapEntry.Id));
				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_3_X, (int)mapEntry.PositionOnMap.X));
				_entrisOnMapAdapter.InsertCommand.Parameters.Add(new SqlParameter(COL_4_Y, (int)mapEntry.PositionOnMap.Y));
				_entrisOnMapAdapter.InsertCommand.ExecuteNonQuery();
			}

			return true;
		}

		/// <summary>
		/// Deletes the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Delete(IMap item)
		{
			/* First we add the map */
			_mapsAdapter.DeleteCommand.Parameters.Clear();
			_mapsAdapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_mapsAdapter.DeleteCommand.ExecuteNonQuery();

			/* And now the entries on map */
			foreach (IMapEntry mapEntry in item.Entries)
			{
				_entrisOnMapAdapter.DeleteCommand.Parameters.Clear();
				_entrisOnMapAdapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_MAP_ID, item.Id));				
				_entrisOnMapAdapter.DeleteCommand.ExecuteNonQuery();
			}

			return true;
		}

		#endregion

		#region Private Members

		private IMapEntry CreateMapEntry(int id, double xPos, double yPos, IList<IEntry> entriesList)
		{
			MapEntry mapEntry = new MapEntry()
			{
				Id = id,
				PositionOnMap = new Position()
				{
					X = xPos,
					Y = yPos
				}
			};

			/* Now find the matching entry */
			foreach (IEntry entry in entriesList)
			{
				if (entry.Id == mapEntry.Id)
				{
					/* Copy all the rest */
					foreach (ICamera camera in entry.Cameras)
						mapEntry.Cameras.Add(camera);

					foreach (ISensor sensor in entry.Sensors)
						mapEntry.Sensors.Add(sensor);

					foreach (IEvent myEvent in entry.Events)
						mapEntry.Events.Add(myEvent);

					mapEntry.Ip = entry.Ip;
					mapEntry.Name = entry.Name;
					mapEntry.PortNumber = entry.PortNumber;
					mapEntry.State = entry.State;

					/* And add it to the map items */
					return mapEntry;
				}
			}

			return null;
	
		}

		#endregion
	}
}
